USE [NLKHCNData_Prod]
GO

/****** Object:  StoredProcedure [dbo].[SearchChuyenGiaAdvance]    Script Date: 1/4/2015 11:59:25 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- 2015-01-04: Hien created
-- EXEC SearchChuyenGiaAdvance 0, 10, 'Ten ASC', '', '', '', 0, 4, '', ''
-- =============================================
CREATE PROCEDURE [dbo].[SearchChuyenGiaAdvance]
	@startIndex int,
	@pageSize int,
	@sorting varchar(50),
	@hoTen nvarchar(255) = '',
	@tenDonVi nvarchar(255) = '',
	@diaChiDonVi nvarchar(255) = '',
	@linhVucId int = 0,
	@nhomLinhVucId int = 0,
	@tenLuanAn nvarchar(255) = '',
	@tenCongTrinh nvarchar(255) = ''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select Id, HoTen, Ten, TenDonVi, DiaChiDonVi, TenLinhVuc, TenNhomLinhVuc, TenLuanAn, CongTrinh
	from
	(select CG.Id, 
		CG.HoTen, 
		CG.Ten, 
		CG.TenDonVi, 
		CG.DiaChiDonVi, 
		CG.TenLuanAn,
		CG.CongTrinh,
		LV.Ten as TenLinhVuc,
		NLV.Ten as TenNhomLinhVuc,
		ROW_NUMBER() Over (
			order by 
				case when @sorting = 'HoTen ASC' then CG.Ten end,
				case when @sorting = 'HoTen DESC' then CG.Ten end DESC) AS RowNumber
	from ChuyenGiaCongTrinhView CG
		left outer join LinhVuc LV on LV.Id = CG.LinhVucId
		left outer join NhomLinhVuc NLV on NLV.Id = LV.NhomLinhVucId
	where 
		(
			(@linhVucId = 0 and @nhomLinhVucId = 0)
			or (@linhVucId > 0 and LinhVucId = @linhVucId) 
			or (@linhVucId = 0 and @nhomLinhVucId > 0 and NLV.Id=@nhomLinhVucId)
		)
		and ((@hoTen = '') or (CG.HoTen like N'%' + @hoTen + '%'))
		and ((@tenDonVi = '') or (CG.TenDonVi like N'%' + @tenDonVi + '%'))
		and ((@diaChiDonVi = '') or (CG.DiaChiDonVi like N'%' + @diaChiDonVi + '%'))
		and ((@tenLuanAn = '') or (TenLuanAn like N'%' + @tenLuanAn + '%'))
		and ((@tenCongTrinh = '') or (CongTrinh like N'%' + @tenCongTrinh + '%'))
	) ChuyenGiaPage
	where RowNumber > @startIndex and RowNumber <= @startIndex + @pageSize
END

GO


